﻿CREATE TABLE [dbo].[Conturi] (
    [Cont]            [dbo].[TipCont] CONSTRAINT [DF_Conturi_Simbol] DEFAULT ('') NOT NULL,
    [CodUnitate]      SMALLINT        CONSTRAINT [DF_Conturi_CodUnitate] DEFAULT ((0)) NOT NULL,
    [Denumire]        VARCHAR (200)   CONSTRAINT [DF_Conturi_Denumire] DEFAULT ('') NOT NULL,
    [Tip]             CHAR (1)        CONSTRAINT [DF_Conturi_Tip] DEFAULT ('A') NOT NULL,
    [Terminal]        BIT             CONSTRAINT [DF_Conturi_Terminal] DEFAULT ((1)) NOT NULL,
    [ContParinte]     [dbo].[TipCont] CONSTRAINT [DF_Conturi_CodContParinte] DEFAULT ('') NOT NULL,
    [EsteAnalitic]    BIT             CONSTRAINT [DF_Conturi_EsteAnalitic] DEFAULT ((1)) NOT NULL,
    [DebitInitial]    DECIMAL (20, 2) CONSTRAINT [DF_Conturi_DebitInitial] DEFAULT ((0)) NOT NULL,
    [CreditInitial]   DECIMAL (20, 2) CONSTRAINT [DF_Conturi_CreditInitial] DEFAULT ((0)) NOT NULL,
    [DebitPrecedent]  DECIMAL (20, 2) CONSTRAINT [DF_Conturi_DebitPrecedent] DEFAULT ((0)) NOT NULL,
    [CreditPrecedent] DECIMAL (20, 2) CONSTRAINT [DF_Conturi_CreditPrecedent] DEFAULT ((0)) NOT NULL,
    [Categorie]       TINYINT         CONSTRAINT [DF_Conturi_Categorie] DEFAULT ((0)) NOT NULL,
    [RulajDebit]      DECIMAL (20, 2) CONSTRAINT [DF_Conturi_RulajDebit] DEFAULT ((0)) NOT NULL,
    [RulajCredit]     DECIMAL (20, 2) CONSTRAINT [DF_Conturi_RulajCredit] DEFAULT ((0)) NOT NULL,
    [Sold]            DECIMAL (20, 2) CONSTRAINT [DF_Conturi_Sold] DEFAULT ((0)) NOT NULL,
    [SortValue]       AS              ([dbo].[GetOrderString]([Cont])),
    CONSTRAINT [PK_Conturi] PRIMARY KEY CLUSTERED ([Cont] ASC, [CodUnitate] ASC) WITH (FILLFACTOR = 90),
    CONSTRAINT [CK_Conturi_SI] CHECK (NOT ([DebitInitial]<>(0) AND [CreditInitial]<>(0))),
    CONSTRAINT [CK_Conturi_Tip] CHECK ([Tip]='A' OR [Tip]='P' OR [Tip]='B'),
    CONSTRAINT [FK_Conturi_Conturi] FOREIGN KEY ([ContParinte], [CodUnitate]) REFERENCES [dbo].[Conturi] ([Cont], [CodUnitate]),
    CONSTRAINT [FK_Conturi_Unitati] FOREIGN KEY ([CodUnitate]) REFERENCES [dbo].[Unitati] ([CodUnitate])
);


GO
CREATE NONCLUSTERED INDEX [IX_Conturi]
    ON [dbo].[Conturi]([ContParinte] ASC) WITH (FILLFACTOR = 90);


GO

CREATE TRIGGER [dbo].[ActSolduri] ON [dbo].[Conturi] 
FOR INSERT, UPDATE,DELETE
AS
BEGIN
SET NOCOUNT ON
DECLARE @Cont AS varchar(50)
DECLARE @ContParinte AS varchar(50)
DECLARE @CodUnitate AS smallint

SELECT @Cont = [Cont],@CodUnitate=[CodUnitate],@ContParinte=[ContParinte] FROM inserted
IF UPDATE(ContParinte)
BEGIN
	UPDATE [Conturi] SET Terminal=0 FROM inserted i WHERE [Conturi].[Cont] = i.[ContParinte] AND [Conturi].[CodUnitate]=i.[CodUnitate] 
		AND ([Conturi].[Cont]<>'N/A')
END
IF UPDATE(CreditInitial) OR UPDATE(DebitInitial) 
BEGIN
	SET NOCOUNT ON;
    DECLARE update_cursor_cont CURSOR FOR  SELECT DISTINCT Cont,CodUnitate FROM (SELECT * FROM inserted) crt
 	OPEN update_cursor_cont
 	FETCH NEXT FROM update_cursor_cont INTO @Cont,@CodUnitate
 	WHILE (@@FETCH_STATUS <> -1)
 	BEGIN
 		IF (@@FETCH_STATUS <> -2)
   		BEGIN   
   			EXEC balcon_UpdateSolduriInitialeConturi @CodUnitate,@Cont
   		END
    	FETCH NEXT FROM update_cursor_cont INTO @Cont,@CodUnitate
 	END
 	CLOSE update_cursor_cont
 	DEALLOCATE update_cursor_cont
		 
END
IF EXISTS(SELECT * FROM deleted)
BEGIN
	SET NOCOUNT ON;
    DECLARE update_cursor_cont CURSOR FOR  SELECT DISTINCT Cont,CodUnitate,ContParinte FROM (SELECT * FROM deleted) crt
 	OPEN update_cursor_cont
 	FETCH NEXT FROM update_cursor_cont INTO @Cont,@CodUnitate,@ContParinte
 	WHILE (@@FETCH_STATUS <> -1)
 	BEGIN
 		IF (@@FETCH_STATUS <> -2)
   		BEGIN   
   			IF NOT EXISTS(SELECT * FROM Conturi WHERE CodUnitate=@CodUnitate AND ContParinte=@ContParinte)
   			BEGIN
   				UPDATE Conturi SET DebitInitial=0,CreditInitial=0 WHERE CodUnitate=@CodUnitate AND Cont=@ContParinte
   			END	
   			EXEC balcon_UpdateSolduriInitialeConturi @CodUnitate,@ContParinte
   		END
    	FETCH NEXT FROM update_cursor_cont INTO @Cont,@CodUnitate,@ContParinte
 	END
 	CLOSE update_cursor_cont
 	DEALLOCATE update_cursor_cont
END
END
GO
CREATE TRIGGER [dbo].[ContActTerminalDelete]
ON [dbo].[Conturi]
FOR DELETE
AS 
BEGIN
UPDATE [Conturi] SET Terminal = 1 FROM deleted d WHERE [Conturi].[Cont] = d.[ContParinte]
    AND [Conturi].[CodUnitate]=d.[CodUnitate]
	AND ([Conturi].[Cont]<>'N/A')
    AND NOT EXISTS(SELECT * FROM [Conturi] c WHERE c.[ContParinte] = d.[ContParinte] AND c.[Cont]<>d.[Cont])

END
GO

CREATE TRIGGER [dbo].[ContActTerminalModify]
   ON  [dbo].[Conturi]
   AFTER UPDATE
AS 
BEGIN
	DECLARE @mesaj varchar(5000)
	DECLARE @contUpdate TipCont
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	IF UPDATE(Terminal)	
	BEGIN
		DECLARE @newValue bit
		DECLARE @CodUnitate smallint 
		SELECT @newValue = [Terminal],@contUpdate=[Cont],@CodUnitate=CodUnitate FROM inserted
		IF @newValue=0 AND 
			EXISTS(SELECT * FROM [Rulaje] r JOIN inserted i ON (r.[ContDebit]=i.[Cont] OR r.[ContCredit]=i.[Cont]) AND r.[CodUnitate]=i.[CodUnitate])
			BEGIN
			--GOTO Error1
				DECLARE @ContAnalitic varchar(50)
				SELECT TOP(1) @ContAnalitic = Cont FROM Conturi WHERE ContParinte=@contUpdate AND CodUnitate=@CodUnitate
				UPDATE Rulaje SET ContCredit=@ContAnalitic WHERE ContCredit=@contUpdate and CodUnitate =@CodUnitate
				UPDATE Rulaje SET ContDebit=@ContAnalitic WHERE ContDebit=@contUpdate and CodUnitate =@CodUnitate
				--UPDATE Conturi SET CreditInitial=a.CreditInitial,DebitInitial=a.DebitInitial FROM Conturi a where a.Cont=@contUpdate AND a.CodUnitate=@CodUnitate AND CodUnitate=@CodUnitate AND Cont=@ContAnalitic
			END
		
	END
	IF UPDATE(ContParinte)
	BEGIN
		UPDATE [Conturi] SET Terminal = 1 FROM deleted d WHERE [Conturi].[Cont] = d.[ContParinte] AND [Conturi].[CodUnitate]=d.[CodUnitate]
		AND ([Conturi].[Cont]<>'N/A')
		AND NOT EXISTS(SELECT * FROM [Conturi] c WHERE c.[ContParinte] = d.[ContParinte] AND c.[Cont]<>d.[Cont] AND c.[CodUnitate]=d.[CodUnitate])
	END
	IF UPDATE(Tip)
	BEGIN
		IF EXISTS(SELECT * FROM inserted i JOIN Conturi c ON i.Cont=c.Cont AND i.CodUnitate=c.CodUnitate
		WHERE i.ContParinte <> 'N/A' AND i.Tip<>c.Tip)
			GOTO Error2
		UPDATE c SET c.Tip = i.tip  FROM [conturi] c, inserted i 
		where c.Cont Like RTRIM(LTRIM(i.Cont)) + '%' AND c.Tip<>i.Tip	
	END
	GOTO Gata
	Error1:
	    SET @mesaj = 'Contul ' + @contUpdate + ' nu poate fi transformat in cont neterminal deoarece are miscari inregistrate!'
		RAISERROR (@mesaj, 16, 1)
		ROLLBACK TRANSACTION
	Error2:
	    SET @mesaj = 'Tipul contului nu se poate modifica decat pentru sinteticele de grad I sau II'
		RAISERROR (@mesaj, 16, 1)
		ROLLBACK TRANSACTION
	Gata:
END
GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Validare sodluri initiale', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Conturi', @level2type = N'CONSTRAINT', @level2name = N'CK_Conturi_SI';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'0-Financiar,1-Gestiune,2-In afara bilantului', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Conturi', @level2type = N'COLUMN', @level2name = N'Categorie';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Valoare Credit initial', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Conturi', @level2type = N'COLUMN', @level2name = N'CreditInitial';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Valoare debit initial', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Conturi', @level2type = N'COLUMN', @level2name = N'DebitInitial';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Codul contului care va insuma rezultatul ', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Conturi', @level2type = N'COLUMN', @level2name = N'ContParinte';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Terminal - daca permite operatiuni pe el', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Conturi', @level2type = N'COLUMN', @level2name = N'Terminal';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Tipul Contului -activ, pasiv, bifunctional', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Conturi', @level2type = N'COLUMN', @level2name = N'Tip';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Denumirea Contului', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Conturi', @level2type = N'COLUMN', @level2name = N'Denumire';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'Simbolul rezultat din cod + puncte unde trebuie', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Conturi', @level2type = N'COLUMN', @level2name = N'Cont';

